﻿using System;
using System.Collections.Generic;
using System.Text;
using PgBirdie.Core.Extensions;
using PgBirdie.Core.Model;
using PgBirdie.Core.Model.Database.Structure;
using PgBirdie.Core.Model.Database.Structure.Constraint;
using PgBirdie.Core.Model.Database.Structure.Index;
using PgBirdie.Core.Service.Compare;
using PgBirdie.Core.Service.DbObject;

namespace PgBirdie.Core.Service.Delta
{
    public class DeltaStructureDbObjectToSqlService : IDeltaStructureDbObjectToSqlService
    {
        private readonly ICompareService _compare;
        private readonly ICompareExtractService _compareExtract;
        private readonly IDbObjectToSqlService _dbObjectToSql;

        public DeltaStructureDbObjectToSqlService(ICompareService compare, ICompareExtractService compareExtract, IDbObjectToSqlService dbObjectToSql)
        {
            _compare = compare;
            _compareExtract = compareExtract;
            _dbObjectToSql = dbObjectToSql;
        }

        public string AddConstraints(IDictionary<string, PgConstraintBase> constraints)
        {
            if (constraints.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Add constraints.{0}", Environment.NewLine);
            foreach (var constraint in constraints.Values)
                sql.Append(_dbObjectToSql.AddConstraint(constraint));

            return sql.ToString();
        }

        public string DropConstraints(IDictionary<string, PgConstraintBase> constraints)
        {
            if (constraints.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Drop constraints.{0}", Environment.NewLine);
            foreach (var constraint in constraints.Values)
                sql.Append(_dbObjectToSql.DropConstraint(constraint));

            return sql.ToString();
        }

        public string DropIndexes(IDictionary<string, PgIndex> indexes)
        {
            if (indexes.IsNullOrEmpty()) return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Drop existing indexes.{0}", Environment.NewLine);
            foreach (var index in indexes)
                sql.Append(_dbObjectToSql.DropIndex(index.Value));

            return sql.ToString();
        }

        public string DropTables(IDictionary<string, PgTable> tables)
        {
            if (tables.IsNullOrEmpty()) return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Drop existing tables.{0}", Environment.NewLine);
            foreach (var table in tables)
                sql.Append(_dbObjectToSql.DropTable(table.Value));

            return sql.ToString();
        }

        public string DropSchemas(IDictionary<string, PgSchema> schemas)
        {
            if (schemas.IsNullOrEmpty()) return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Drop existing schemas.{0}", Environment.NewLine);
            foreach (var schema in schemas.Values)
                sql.Append(_dbObjectToSql.DropSchema(schema));

            return sql.ToString();
        }

        public string CreateIndexes(IDictionary<string, PgIndex> indexes)
        {
            if (indexes.IsNullOrEmpty()) return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Create indexes.{0}", Environment.NewLine);
            foreach (var index in indexes)
                sql.Append(_dbObjectToSql.CreateIndex(index.Value));

            return sql.ToString();
        }

        public string CreateSchemas(IDictionary<string, PgSchema> schemas)
        {
            if (schemas.IsNullOrEmpty()) return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Create schemas.{0}", Environment.NewLine);
            foreach (var schema in schemas.Values)
                sql.Append(_dbObjectToSql.CreateSchema(schema));

            return sql.ToString();
        }

        public string CreateSequences(IDictionary<string, PgSequence> sequences)
        {
            if (sequences.IsNullOrEmpty()) return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Create sequences.{0}", Environment.NewLine);
            foreach (var sequence in sequences.Values)
                sql.Append(_dbObjectToSql.CreateSequence(sequence));

            return sql.ToString();
        }

        public string CreateTables(IDictionary<string, PgTable> tables)
        {
            if (tables.IsNullOrEmpty()) return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Create tables.{0}", Environment.NewLine);
            foreach (var table in tables.Values)
            {
                sql.Append(_dbObjectToSql.CreateTable(table));

                foreach (var column in table.Columns.Values)
                    sql.Append(_dbObjectToSql.AssociateTableColumnWithSequence(column));
            }

            return sql.ToString();
        }

        public string DropColumns(IDictionary<string, PgColumn> columns)
        {
            if (columns.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Drop columns.{0}", Environment.NewLine);
            foreach (var column in columns)
                sql.Append(_dbObjectToSql.DropColumn(column.Value));

            return sql.ToString();
        }

        public string AddColumns(IDictionary<string, PgColumn> columns)
        {
            if (columns.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Add columns.{0}", Environment.NewLine);
            foreach (var column in columns)
                sql.Append(_dbObjectToSql.AddColumn(column.Value));

            return sql.ToString();
        }

        public string AlterColumns(IDictionary<string, PgColumn> sourceColumns, IDictionary<string, PgColumn> targetColumns)
        {
            if (sourceColumns.IsNullOrEmpty() || targetColumns.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("-- Alter columns.{0}", Environment.NewLine);
            foreach (var column in sourceColumns.Values)
            {
                var targetColumn = targetColumns[column.Name];
                sql.Append(_dbObjectToSql.AlterColumn(column, targetColumn));
            }

            return sql.ToString();
        }

        public string AlterTables(IDictionary<string, PgTable> tables, PgDatabase targetDb)
        {
            if (tables.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder();

            foreach (var table in tables)
            {
                var schemaName = table.Value.Schema.Name;
                var tableName = table.Value.Name;

                var columns = _compareExtract.GetColumnsFromTable(table.Value, targetDb.Schemas[schemaName].Tables[tableName]);

                if (columns[PersistenceType.Delete].IsNotNullOrEmpty())
                {
                    foreach (var column in columns[PersistenceType.Delete])
                        sql.Append(_dbObjectToSql.DropColumn(column.Value));
                }

                if (columns[PersistenceType.Add].IsNotNullOrEmpty())
                {
                    foreach (var column in columns[PersistenceType.Add])
                        sql.Append(_dbObjectToSql.AddColumn(column.Value));
                }

                if (columns[PersistenceType.Keep].IsNotNullOrEmpty())
                {
                    foreach (var column in columns[PersistenceType.Keep])
                    {
                        var targetColumn = targetDb.Schemas[schemaName].Tables[tableName].Columns[column.Key];

                        var columnPropertiesMatch = _compare.ColumnPropertiesMatch(column.Value, targetColumn);

                        if (columnPropertiesMatch)
                            continue;

                        sql.Append(_dbObjectToSql.AlterColumn(column.Value, targetColumn));
                    }
                }
            }

            var result = sql.ToString();

            return !String.IsNullOrEmpty(result) ? String.Format("-- Alter existing tables.{0}{1}", Environment.NewLine, result) : String.Empty;
        }
    }
}